﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace ChuongtrinhQLHSTHPT
{
    class Connectionclass
    {
         string constr = ChuongtrinhQLHSTHPT.Properties.Settings.Default.QLHSConnectionString;
        SqlConnection con;
        public Connectionclass()
        {
            constr = ChuongtrinhQLHSTHPT.Properties.Settings.Default.QLHSConnectionString;
            con = new SqlConnection(constr);
        }
        public bool IsConnect()
        {
            bool isConected = false;
            try
            {
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    isConected = true;
                }
            }
            catch (Exception)
            {
            }
            return isConected;
        }
        #region Hồ sơ học sinh
        public DataTable LoadData()
        {
            DataTable datam = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select * from HOCSINH";
                com.CommandType = CommandType.Text;
                com.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datam);
            }
            return datam;
        }
        public DataTable Login(string user, string pass)
        {
            DataTable datb = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "dangnhap";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_username", user);
                com.Parameters.AddWithValue("@_password", pass);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datb);
            }
            return datb;
        }
        public bool Inserthischool(int MAHS,string HOTEN,DateTime NGAYSINH,string GIOITINH,string NOISINH,string DANTOC,int LOPHOC,string HOTENBO,string NGHENGHIEPBO,string HOTENME,string NGHENGHIEPME)
        {
            bool isInsert = true;
            if(IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "themhocsinh";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_MAHS",MAHS);
                cmd.Parameters.AddWithValue("@_HOTEN", HOTEN);
                cmd.Parameters.AddWithValue("@_NGAYSINH", NGAYSINH);
                cmd.Parameters.AddWithValue("@_GIOITINH", GIOITINH);
                cmd.Parameters.AddWithValue("@_NOISINH", NOISINH);
                cmd.Parameters.AddWithValue("@_DANTOC", DANTOC);
                cmd.Parameters.AddWithValue("@_LOPHOC", LOPHOC);
                cmd.Parameters.AddWithValue("@_HOTENBO", HOTENBO);
                cmd.Parameters.AddWithValue("@_NGHENGHIEPBO", NGHENGHIEPBO);
                cmd.Parameters.AddWithValue("@_HOTENME",HOTENME);
                cmd.Parameters.AddWithValue("@_NGHENGHIEPME", NGHENGHIEPME);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if(i > 0)
                {
                    isInsert = true;
                }
            }
            return isInsert;
        }
        public bool Updatehischool(int MAHS, string HOTEN, DateTime NGAYSINH, string GIOITINH, string NOISINH, string DANTOC, int LOPHOC, string HOTENBO, string NGHENGHIEPBO, string HOTENME, string NGHENGHIEPME)
        {
            bool isupdate = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "capnhathocsinh";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@_MAHS", MAHS);
                cmd.Parameters.AddWithValue("@_HOTEN", HOTEN);
                cmd.Parameters.AddWithValue("@_NGAYSINH", NGAYSINH);
                cmd.Parameters.AddWithValue("@_GIOITINH", GIOITINH);
                cmd.Parameters.AddWithValue("@_NOISINH", NOISINH);
                cmd.Parameters.AddWithValue("@_DANTOC", DANTOC);
                cmd.Parameters.AddWithValue("@_LOPHOC", LOPHOC);
                cmd.Parameters.AddWithValue("@_HOTENBO", HOTENBO);
                cmd.Parameters.AddWithValue("@_NGHENGHIEPBO", NGHENGHIEPBO);
                cmd.Parameters.AddWithValue("@_HOTENME", HOTENME);
                cmd.Parameters.AddWithValue("@_NGHENGHIEPME", NGHENGHIEPME);
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isupdate = true;
                }
            }
            return isupdate;
        }
        public bool Deletehischool(int MAHS)
        {
            bool IsDelete = false;
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "Xoahocsinh";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_MAHS", MAHS);
                int i = com.ExecuteNonQuery();
                if (i > 0) IsDelete = true;

            }
            return IsDelete;
        }
        public DataTable Searchhocsinh(string HOTEN)
        {
            DataTable datb = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "Searchhocsinh";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_HOTEN", HOTEN);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datb);
            }
            return datb;
        }
        #endregion
        #region Lớp học
        public DataTable Loaddslop()
        {
            DataTable data = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select * from LOP";
                com.CommandType = CommandType.Text;
                com.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(data);
            }
            return data;
        }
        public bool InsertClass(int MALOP,string TENLOP,int SISO,int GVCN)
        {
            bool isInsert = true;
            if(IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Insertlop";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_Malop",MALOP);
                cmd.Parameters.AddWithValue("@_Tenlop",TENLOP);
                cmd.Parameters.AddWithValue("@_Siso", SISO);
                cmd.Parameters.AddWithValue("@_Gvcn", GVCN);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if(i >0)
                {
                    isInsert = true;
                }
            }
            return isInsert;
        }
        public bool UpdateClass(int MALOP, string TENLOP, int SISO, int GVCN)
        {
            bool isUpdate = true;
            if(IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "updatelop";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_Malop",MALOP);
                cmd.Parameters.AddWithValue("@_Tenlop",TENLOP);
                cmd.Parameters.AddWithValue("@_Siso",SISO);
                cmd.Parameters.AddWithValue("@_Gvcn",GVCN);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isUpdate = true;
                }
            }
            return isUpdate;
        }
        public bool DeleteClass(int MALOP)
        {
            bool isDelete = true;
            if(IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "deletelop";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_Malop", MALOP);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if(i > 0)
                {
                    isDelete = true;
                }
            }
            return isDelete;
        }
        public DataTable Searchlop(string TENLOP)
        {
            DataTable data = new DataTable();
            if(IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Searchlop";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_Tenlop",TENLOP);
                cmd.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(data);
            }
            return data;
        }
        #endregion
        #region Tra cứu kết quả học tập
        public DataTable Loadtracuukqht()
        {
            DataTable data = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select HOCSINH.MAHS,HOTEN ,NGAYSINH,GIOITINH,NOISINH,TENLOP,TENMON,DIEM15P,DIEM45P,DIEMTHI,((DIEM15P+DIEM45P*2+DIEMTHI*7)/10) AS DIEMTK, HANHKIEM, XEPLOAI from HOCSINH,DD_DIEM,BANGDIEM,DD_KQHT,LOP,MONHOC, KQHOCTAP  where HOCSINH.LOPHOC=LOP.MALOP and BANGDIEM.MAHOCSINH= HOCSINH.MAHS and BANGDIEM.MABANGDIEM= DD_DIEM.MADIEM and DD_DIEM.MONHOC= MONHOC.MAMON and DD_KQHT.MAHS=HOCSINH.MAHS and DD_KQHT.MAKQHT = KQHOCTAP.MAKQ";
                com.CommandType = CommandType.Text;
                com.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(data);
            }
            return data;
        }
        public DataTable Tracuutheoma(int MAHS)
        {
            DataTable datble = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "tracuutheoma";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_MAHS",MAHS);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datble);
            }
            return datble;
        }
        public DataTable Tracuutheohoten(string HOTEN)
        {
            DataTable datble = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "tracuutheohoten";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_HOTEN", HOTEN);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datble);
            }
            return datble;
        }
        #endregion
        #region Danh sách giáo viên chủ nhiệm
        public DataTable Loaddsgvcn()
        {
            DataTable data = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select MALOP,TENLOP,MAGV,HOTEN,NGAYSINH from LOP,GIAOVIEN where LOP.GVCN = GIAOVIEN.MAGV";
                com.CommandType = CommandType.Text;
                com.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(data);
            }
            return data;
        }
        public bool UpdateGVCN(int MALOP, int MAGV)
        {
            bool isUpdate = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "updateGVCN";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_Malop", MALOP);
                cmd.Parameters.AddWithValue("@_Gvcn", MAGV);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isUpdate = true;
                }
            }
            return isUpdate;
        }
        #endregion
        #region Lập thời khóa biểu
        public DataTable Loadtkb()
        {
            DataTable data = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select MATKB, LOP, TENLOP, MONHOC, TENMON, GVGIANGDAY, GIAOVIEN.HOTEN from THOIKHOABIEU, LOP, MONHOC, GIAOVIEN where THOIKHOABIEU.LOP = LOP.MALOP and THOIKHOABIEU.MONHOC = MONHOC.MAMON and THOIKHOABIEU.GVGIANGDAY = GIAOVIEN.MAGV";
                com.CommandType = CommandType.Text;
                com.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(data);
            }
            return data;
        }
        public bool InsertTKB(int MATKB, int LOP, int MONHOC, int GVGIANGDAY)
        {
            bool isInsert = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Inserttkb";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_matkb", MATKB);
                cmd.Parameters.AddWithValue("@_lop", LOP);
                cmd.Parameters.AddWithValue("@_monhoc", MONHOC);
                cmd.Parameters.AddWithValue("@_gvgiangday", GVGIANGDAY);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isInsert = true;
                }
            }
            return isInsert;
        }
        public bool Updatetkb(int MATKB, int LOP, int MONHOC, int GVGIANGDAY)
        {
            bool isUpdate = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Updatetkb";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_matkb", MATKB);
                cmd.Parameters.AddWithValue("@_lop", LOP);
                cmd.Parameters.AddWithValue("@_monhoc", MONHOC);
                cmd.Parameters.AddWithValue("@_gvgiangday", GVGIANGDAY);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isUpdate = true;
                }
            }
            return isUpdate;
        }
        public bool Deletetkb(int MATKB)
        {
            bool isDelete = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Deletetkb";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_matkb", MATKB);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isDelete = true;
                }
            }
            return isDelete;
        }
        public DataTable Searchtkb(int MATKB)
        {
            DataTable data = new DataTable();
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Searchtkb";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_matkb", MATKB);
                cmd.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(data);
            }
            return data;
        }
        #endregion
        #region Lập kết quả học tập
        public DataTable Loadkqht()
        {
            DataTable data = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select MAKQ,HOCSINH.MAHS,HOCSINH.HOTEN, HOCSINH.NGAYSINH, DIEM15P, DIEM45P, DIEMTHI, ((DIEM15P+DIEM45P*2+DIEMTHI*7)/10) AS DIEMTK, HOCLUC, HANHKIEM,KQHOCTAP.XEPLOAI, KQHOCTAP.NAMHOC, NHANXET From KQHOCTAP, DD_KQHT, HOCSINH, BANGDIEM, DD_DIEM where KQHOCTAP.MAKQ = DD_KQHT.MAKQHT and HOCSINH.MAHS = DD_KQHT.MAHS and HOCSINH.MAHS = BANGDIEM.MAHOCSINH and DD_DIEM.MADIEM = BANGDIEM.MABANGDIEM";
                com.CommandType = CommandType.Text;
                com.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(data);
            }
            return data;
        }
        public bool InsertKQHT(int MAKQ, int MAHS, string HOCLUC, string HANHKIEM, string XEPLOAI, int NAMHOC, string NHANXET)
        {
            bool isInsert = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Insertkqht";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_makq", MAKQ);
                cmd.Parameters.AddWithValue("@_mahs", MAHS);
                cmd.Parameters.AddWithValue("@_hocluc", HOCLUC);
                cmd.Parameters.AddWithValue("@_hanhkiem", HANHKIEM);
                cmd.Parameters.AddWithValue("@_xeploai", XEPLOAI);
                cmd.Parameters.AddWithValue("@_namhoc", NAMHOC);
                cmd.Parameters.AddWithValue("@_nhanxet", NHANXET);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isInsert = true;
                }
            }
            return isInsert;
        }
        public bool Updatekqht(int MAKQ, int MAHS, string HOCLUC, string HANHKIEM, string XEPLOAI, int NAMHOC, string NHANXET)
        {
            bool isUpdate = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Updatekqht";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_makq", MAKQ);
                cmd.Parameters.AddWithValue("@_mahs", MAHS);
                cmd.Parameters.AddWithValue("@_hocluc", HOCLUC);
                cmd.Parameters.AddWithValue("@_hanhkiem", HANHKIEM);
                cmd.Parameters.AddWithValue("@_xeploai", XEPLOAI);
                cmd.Parameters.AddWithValue("@_namhoc", NAMHOC);
                cmd.Parameters.AddWithValue("@_nhanxet", NHANXET);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isUpdate = true;
                }
            }
            return isUpdate;
        }
        public bool Deletekqht(int MAKQ)
        {
            bool isDelete = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Deletekqht";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_makq", MAKQ);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isDelete = true;
                }
            }
            return isDelete;
        }
        public DataTable Searchkqht(int MAHS)
        {
            DataTable datble = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "Searchkqht";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_mahs", MAHS);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datble);
            }
            return datble;
        }
        #endregion
        #region Khen thưởng
         public DataTable LoadKhenthuong()
        {
            DataTable datam = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select A.MAKHENTHUONG,MAHS,A.DANHHIEU,NOIDUNG,NGAYKT,GHICHU from DD_KHENTHUONG as A ,BANGKHENTHUONG where A.MAKHENTHUONG= BANGKHENTHUONG.MAKHENTHUONG";
                com.CommandType = CommandType.Text;
                com.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datam);
            }
            return datam;
        }
        public bool InsertKhenthuong(int MAKHENTHUONG,string NOIDUNG,string DANHHIEU,string GHICHU,int MAHS,DateTime NGAYKT)
        {
            bool isInsert = true;
            if(IsConnect())
            {
              SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Themkhenthuong";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_makhenthuong",MAKHENTHUONG);
                cmd.Parameters.AddWithValue("@_noidung", NOIDUNG);
                cmd.Parameters.AddWithValue("@_danhhieu", DANHHIEU);
                cmd.Parameters.AddWithValue("@_ghichu", GHICHU);
                cmd.Parameters.AddWithValue("@_mahs", MAHS);
                cmd.Parameters.AddWithValue("@_ngaykt", NGAYKT);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if(i > 0)
                {
                    isInsert = true;
                }
            }
            return isInsert;
        }
        public bool UpdateKhenthuong(int MAKHENTHUONG, string NOIDUNG, string DANHHIEU, string GHICHU, int MAHS, DateTime NGAYKT)
        {
            bool isupdate = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Suakhenhuong";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@_makhenthuong", MAKHENTHUONG);
                cmd.Parameters.AddWithValue("@_noidung", NOIDUNG);
                cmd.Parameters.AddWithValue("@_danhhieu", DANHHIEU);
                cmd.Parameters.AddWithValue("@_ghichu", GHICHU);
                cmd.Parameters.AddWithValue("@_mahs", MAHS);
                cmd.Parameters.AddWithValue("@_ngaykt", NGAYKT); 
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isupdate = true;
                }
            }
            return isupdate;
        }
        public bool DeleteKhenthuong(int MAKT)
        {
            bool IsDelete = false;
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "XoaKhenthuong";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_makhenthuong",MAKT);
                int i = com.ExecuteNonQuery();
                if (i > 0) IsDelete = true;

            }
            return IsDelete;
        }
        public DataTable SearchKhenthuong(int MAKT)
        {
            DataTable datb = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "Searchkhenthuong";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_makhenthuong",MAKT);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datb);
            }
            return datb;
        }
        #endregion
        #region Kỉ luật
         public DataTable LoadKyluat()
        {
            DataTable datam = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "select A.MAKYLUAT,MAHS,NGAYKL,NOIDUNG,A.LIDO,HINHTHUC,GHICHU from BANGKYLUAT ,DD_KYLUAT as A where BANGKYLUAT.MAKYLUAT= A.MAKYLUAT";
                com.CommandType = CommandType.Text;
                com.Connection = con;
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datam);
            }
            return datam;
        }
         public bool InsertKyluat(int MAKYLUAT, int MAHS, DateTime NGAYKL, string LIDO, string NOIDUNG, string GHICHU,string HINHTHUC)
        {
            bool isInsert = true;
            if(IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Themkiluat";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@_makyluat", MAKYLUAT);
                cmd.Parameters.AddWithValue("@_mahs", MAHS);
                cmd.Parameters.AddWithValue("@_ngaykl", NGAYKL);
                cmd.Parameters.AddWithValue("@_noidung", NOIDUNG);
                cmd.Parameters.AddWithValue("@_lido", LIDO);
                cmd.Parameters.AddWithValue("@_hinhthuc", HINHTHUC);
                cmd.Parameters.AddWithValue("@_ghichu", GHICHU);
                cmd.Connection = con;
                int i = cmd.ExecuteNonQuery();
                if(i > 0)
                {
                    isInsert = true;
                }
            }
            return isInsert;
        }
         public bool UpdateKyluat(int MAKYLUAT, int MAHS, DateTime NGAYKL, string LIDO, string NOIDUNG, string GHICHU, string HINHTHUC)
        {
            bool isupdate = true;
            if (IsConnect())
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Suakyluat";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("@_makyluat", MAKYLUAT);
                cmd.Parameters.AddWithValue("@_mahs", MAHS);
                cmd.Parameters.AddWithValue("@_ngaykl", NGAYKL);
                cmd.Parameters.AddWithValue("@_noidung", NOIDUNG);
                cmd.Parameters.AddWithValue("@_lido", LIDO);
                cmd.Parameters.AddWithValue("@_hinhthuc", HINHTHUC);
                cmd.Parameters.AddWithValue("@_ghichu", GHICHU);
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    isupdate = true;
                }
            }
            return isupdate;
        }
        public bool DeleteKyluat(int MAKL)
        {
            bool IsDelete = false;
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "XoaKyluat";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_makiluat", MAKL);
                int i = com.ExecuteNonQuery();
                if (i > 0) IsDelete = true;

            }
            return IsDelete;
        }
        public DataTable SearchKyluat(int MaKL)
        {
            DataTable datb = new DataTable();
            if (IsConnect())
            {
                SqlCommand com = new SqlCommand();
                com.CommandText = "Searchkyluat";
                com.CommandType = CommandType.StoredProcedure;
                com.Connection = con;
                com.Parameters.AddWithValue("@_makiluat", MaKL);
                SqlDataAdapter adap = new SqlDataAdapter(com);
                adap.Fill(datb);
            }
            return datb;
        }
        #endregion
    }
}
